/************************************************************************************
* SLDbUpdater.cpps                                                                   *
*                                                                                   *
* Copyright (C) 2011 Varuna L Amachi                                                *
*                                                                                   *
* This program is free software; you can redistribute it and/or modify              *
* it under the terms of the GNU General Public License as published by              *
* the Free Software Foundation; either version 2 of the License, or any             *
* later version.                                                                    *
*                                                                                   *
* This program is distributed in the hope that it will be useful, but               *
* WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY        *
* or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for       *
* more details. You should have received a copy of the GNU General Public           *
* License along with this program; if not, write to the Free Software Foundation,   *
* Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA                       *
*                                                                                   *
*************************************************************************************/

#include "SLDbUpdater.h"
#include "common/Utils.h"
#include "common/YLog.h"

#include <QDateTime>
#include <QSqlResult>
#include <QSqlError>
#include <QVariantList>

SLDbUpdater::SLDbUpdater()
{
}


//PlaylistInfo {
//  playlistName TEXT PRIMARY KEY
//  creationTime INTEGER
//  lastModified INTEGER
//  rating       INTEGER
//  comment      TEXT
//}
bool SLDbUpdater::addPlaylistInfo(QString playlistName,
                                  QSqlDatabase *database) {
    QString statement("INSERT INTO PlaylistInfo (playlistName, creationTime, "
                      "lastModified, rating, comment) VALUES (?, ?, ?, ?);");
    QSqlQuery *query = getQuery(database);
    quint64 msecs = QDateTime::currentMSecsSinceEpoch();
    query->prepare(statement);
    query->addBindValue(playlistName);
    query->addBindValue(msecs);
    query->addBindValue(msecs);
    query->addBindValue(0);
    query->addBindValue("");
    bool result = query->exec();
    YLog::logQuery("addPlaylistInfo", query);
    delete query;
    return result;
}


//Playlists {
//  playlistName TEXT
//  trackId      -->	Collection.trackId
//}
bool SLDbUpdater::addPlaylistItem(QString playlistName, QString trackId,
                     QSqlDatabase *database) {
    QString statement("INSERT INTO Playlists (playlistName, trackId) "
                      "VALUES (?, ?)");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    query->addBindValue(playlistName);
    query->addBindValue(trackId);
    bool result = query->exec();
    YLog::logQuery("addPlaylistItem", query);
    delete query;
    return result;
}




//Collection {
//  trackId 	TEXT PRIMERY KEY
//  trackPath 	TEXT
//  trackName 	TEXT
//  artistName 	TEXT
//  albumName 	TEXT
//  trackDate 	TEXT
//  genre 	TEXT
//  comment 	TEXT
//  trackNum 	INTEGER
//  duration 	INTEGER
//  bitrate 	INTEGER
//  sampleRate 	INTEGER
//  channels 	INTEGER
//  rating 	INTEGER
//  numPlays 	INTEGER
//  tags		TEXT
//}
bool SLDbUpdater::addCollectionItem(CollectorItem *item,
                                    QSqlDatabase *database) {
    QString statement("INSERT INTO Collection  (trackId,"
                      "trackPath, trackName, artistName, albumName, "
                      "trackDate, genre, comment, trackNum, "
                      "duration, bitrate, sampleRate, channels, "
                      "rating, numPlays, tags) VALUES ("
                      "?, ?, ?, ?,"
                      "?, ?, ?, ?,"
                      "?, ?, ?, ?,"
                      "?, ?, ?, ?);");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    query->addBindValue(item->trackId());
    query->addBindValue(item->filePath());
    query->addBindValue(item->title());
    query->addBindValue(item->artist());
    query->addBindValue(item->album());
    query->addBindValue(item->date());
    query->addBindValue(item->genre());
    query->addBindValue(item->description());
    query->addBindValue(item->trackNumber());
    query->addBindValue(item->duration());
    query->addBindValue(item->bitrate());
    query->addBindValue(item->sampleRate());
    query->addBindValue(item->numChannels());
    query->addBindValue(Utils::getIntegerFromRating(item->rating()));
    query->addBindValue(item->playCount());
    query->addBindValue(item->tags());
    bool result = query->exec();
    YLog::logQuery("addCollectionItem", query);
    delete query;
    return result;
}



bool SLDbUpdater::addCollectionItems(QList<CollectorItem*> &collection,
                        QSqlDatabase *database) {
    QVariantList idList;
    QVariantList pathList;
    QVariantList titleList;
    QVariantList artistList;
    QVariantList albumList;
    QVariantList dateList;
    QVariantList genreList;
    QVariantList commentList;
    QVariantList trackNumList;
    QVariantList durationList;
    QVariantList bitrateList;
    QVariantList sampleRateList;
    QVariantList channelList;
    QVariantList ratingList;
    QVariantList numPlaysList;
    QVariantList tagList;

    QString statement("INSERT INTO Collection  (trackId,"
                      "trackPath, trackName, artistName, albumName, "
                      "trackDate, genre, comment, trackNum, "
                      "duration, bitrate, sampleRate, channels, "
                      "rating, numPlays, tags) VALUES ("
                      "?, ?, ?, ?,"
                      "?, ?, ?, ?,"
                      "?, ?, ?, ?,"
                      "?, ?, ?, ?);");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    for(int i = 0; i < collection.size(); i++) {
        CollectorItem *item = collection.at(i);
        idList          << item->trackId();
        pathList        << item->filePath();
        titleList       << item->title();
        artistList      << item->artist();
        albumList       << item->album();
        dateList        << item->date();
        genreList       << item->genre();
        commentList     << item->description();
        trackNumList    << item->trackNumber();
        durationList    << item->duration();
        bitrateList     << item->bitrate();
        sampleRateList  << item->sampleRate();
        channelList     << item->numChannels();
        ratingList      << Utils::getIntegerFromRating(item->rating());
        numPlaysList    << item->playCount();
        tagList         << item->tags();
    }
    query->addBindValue( idList);
    query->addBindValue( pathList);
    query->addBindValue( titleList);
    query->addBindValue( artistList);
    query->addBindValue( albumList);
    query->addBindValue( dateList);
    query->addBindValue( genreList);
    query->addBindValue( commentList);
    query->addBindValue( trackNumList);
    query->addBindValue( durationList);
    query->addBindValue( bitrateList);
    query->addBindValue( sampleRateList);
    query->addBindValue( channelList);
    query->addBindValue( ratingList);
    query->addBindValue( numPlaysList);
    query->addBindValue( tagList);

    bool result = query->execBatch();
    YLog::logQuery("addCollectionItems", query);
    delete query;
    return result;
}



//RecentAdditions {QStringBuilder<QString, QString>
//  addedTime 	INTEGER
//  trackId 	 -->  	Collection.trackId
//}
//bool SLDbUpdater::addRecentAdditions(QList<CollectorItem*> &items,
//                        QSqlDatabase *database) {

//    QString deleteStatement("DELETE FROM RecentAdditions;");
//    QSqlQuery *query = getQuery(database);
//    query->exec(deleteStatement);
//    YLog::logQuery("addRecentAdditions[DELETE]", query);
//    delete query;

//    //Again, bulk insert would be better.
//    QString statement("INSERT INTO RecentAdditions ("
//                      "addedTime, trackId) VALUES (?, ?);");
//    for(int i = 0; i < items.size(); i++) {
//        QSqlQuery *query = getQuery(database);
//        query->prepare(statement);
//        query->addBindValue(QDateTime::currentMSecsSinceEpoch());
//        query->addBindValue(items.at(i)->trackId());
//        if( ! query->exec()) {
//            YLog::logQuery("addRecentAdditions", query);
//            delete query;
//            return false;
//        }
//        delete query;
//    }
//    YLog::logDebug("addRecentAdditions successfull");
//    return true;
//}


bool SLDbUpdater::addRecentAddition(CollectorItem *item,
                        QSqlDatabase *database) {

//    QString deleteStatement("DELETE FROM RecentAdditions;");
//    QSqlQuery *query = getQuery(database);
//    query->exec(deleteStatement);
//    YLog::logQuery("addRecentAdditions[DELETE]", query);
//    delete query;

    QString statement("INSERT INTO RecentAdditions ("
                      "addedTime, trackId) VALUES (?, ?);");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    query->addBindValue(QDateTime::currentMSecsSinceEpoch());
    query->addBindValue(item->trackId());
    if( ! query->exec()) {
        YLog::logQuery("addRecentAdditions", query);
        delete query;
        return false;
    }
    delete query;
//    YLog::logDebug("addRecentAdditions successfull");
    return true;
}


//PlayHistory {
//  lastPlayedTime INTEGER
//  trackId 	 -->  	Collection.trackId
//}
bool SLDbUpdater::addHistory(CollectorItem *item,
                        QSqlDatabase *database) {
    QString selectStatement("SELECT * FROM PlayHistory WHERE trackId = ?");
    QSqlQuery *query = getQuery(database);
    query->prepare(selectStatement);
    query->addBindValue(item->trackId());
    bool result = query->exec();
    YLog::logQuery("addHistory[EXISTS]", query);
    if( result ) {
        bool exists = query->next();
        delete query;
        if(exists) {
            QString updateStatement("UPDATE PlayHistory SET "
                                 "lastPlayedTime = ? WHERE trackId = ?");
            QSqlQuery *query = getQuery(database);
            query->prepare(updateStatement);
            query->addBindValue(QDateTime::currentMSecsSinceEpoch());
            query->addBindValue(item->trackId());
            result = query->exec();
            YLog::logQuery("addHistory[UPDATE]", query);
            delete query;
        }
        else {
            QString insertStatement("INSERT INTO PlayHistory (lastPlayedTime, trackId) "
                              "VALUES (?, ?);");
            QSqlQuery *query = getQuery(database);
            query->prepare(insertStatement);
            query->addBindValue(QDateTime::currentMSecsSinceEpoch());
            query->addBindValue(item->trackId());
            result = query->exec();
            YLog::logQuery("addHistory[INSERT]", query);
            delete query;
        }
    }
    return result;
}


//Settings {
//  settingsKey 	TEXT PRIMARY KEY
//  settingsValue TEXT
//}
bool SLDbUpdater::addSetting(QString key, QString value,
                        QSqlDatabase *database) {
    QString statement("INSERT INTO Settings (settingKey, settingValue) "
                      "VALUES (?, ?);");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    query->addBindValue(key);
    query->addBindValue(value);
    bool result = query->exec();
    YLog::logQuery("addSetting", query);
    delete query;
    return result;
}





bool SLDbUpdater::clearCollection(QSqlDatabase *database) {
    QString statement("DELETE FROM Collection;");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    bool result = query->exec();
    YLog::logQuery("clearCollection", query);
    delete query;
    return result;
}

bool SLDbUpdater::clearHistory(QSqlDatabase *database) {
    QString statement("DELETE FROM PlayHistory;");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    bool result = query->exec();
    YLog::logQuery("clearHistory", query);
    delete query;
    return result;
}

bool SLDbUpdater::clearRecentAddtions(QSqlDatabase *database) {
    QString statement("DELETE FROM RecentAdditions;");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    bool result = query->exec();
    YLog::logQuery("clearRecentAddtions", query);
    delete query;
    return result;
}

//Playlists {
//  playlistName TEXT
//  trackId      -->	Collection.trackId
//}
bool SLDbUpdater::removePlaylistItem(QString playlist, QString trackId,
                        QSqlDatabase *database) {
    QString statement("DELETE FROM Playlists WHERE playlistName = ? AND trackId = ?;");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    query->addBindValue(playlist);
    query->addBindValue(trackId);
    bool result = query->exec();
    YLog::logQuery("removePlaylistItem", query);
    delete query;
    return result;
}


bool SLDbUpdater::removeCollectionItem(QString trackId,
                        QSqlDatabase *database) {
    QString statement("DELETE FROM Collection WHERE trackId = ?;");
//    QString statement("SELECT * from Collection WHERE trackId = ?");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    query->addBindValue(trackId);
    bool result = query->exec();
    YLog::logQuery("removeCollectionItem", query);
    delete query;
    return result;
}

bool SLDbUpdater::removeCollectionItems(QList<QString>* trackIds,
                           QSqlDatabase *database) {
    //Optimize, if possible
    for(int i = 0; i < trackIds->size(); i++) {
        if(! removeCollectionItem(trackIds->at(i), database)) {
            return false;
        }
    }
    return true;
}


bool SLDbUpdater::removePlaylist(QString playlistName,
                        QSqlDatabase *database) {
    //We have to remove the playlist member entries from the Playlists
    //table and then delete the PlaylistInfo entry.

    QString statementOne("DELETE FROM Playlists WHERE playlistName = ?;");
    QSqlQuery *query = getQuery(database);
    query->prepare(statementOne);
    query->addBindValue(playlistName);
    bool result = query->exec();
    YLog::logQuery("removePlaylist", query);
    delete query;

    QString statementTwo("DELETE FROM PlaylistInfo WHERE playlistName = ?;");
    query = getQuery(database);
    query->prepare(statementTwo);
    result &= query->exec();
    YLog::logQuery("removePlaylist", query);
    delete query;

    return result;
}



bool SLDbUpdater::attachTagToItem(QString tagName,
                                  CollectorItem *item,
                                  QSqlDatabase *database) {
    QSet<QString> *tags = getTagsFor(item);
    tags->insert(tagName);
    bool result = updateTagFor(item, tags, database);
    delete tags;
    return result;
}

bool SLDbUpdater::removeTagFromItem(QString tag, CollectorItem *item,
                        QSqlDatabase *database) {
    QSet<QString> *tags = getTagsFor(item);
    tags->remove(tag);
    bool result = updateTagFor(item, tags, database);
    delete tags;
    return result;
}

bool SLDbUpdater::updateTrackRating(CollectorItem *item, int rating,
                       QSqlDatabase *database) {
    QString statement("UPDATE Collection SET rating = ? WHERE trackId = ?;");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    query->addBindValue(rating);
    query->addBindValue(item->trackId());
    bool result = query->exec();
    YLog::logQuery("updateTrackRating", query);
    delete query;
    return result;
}

bool SLDbUpdater::incrementPlayCount(CollectorItem *item,
                       QSqlDatabase *database) {
    int newCount = item->playCount() + 1;
    QString statement("UPDATE Collection SET numPlays = ? WHERE trackId = ?;");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    query->addBindValue(newCount);
    query->addBindValue(item->trackId());
    bool result = query->exec();
    YLog::logQuery("incrementPlayCount", query);
    delete query;
    return result;
}

//PlaylistInfo {
//  playlistName TEXT PRIMARY KEY
//  creationTime INTEGER
//  lastModified INTEGER
//  rating       INTEGER
//  comment      TEXT
//}
bool SLDbUpdater::updatePlaylistInfo(SavedPlaylist *pl,
                                QSqlDatabase *database) {
    QString statement("UPDATE PlaylistInfo SET lastModified = ?, rating = ?, comment = ? "
                      "WHERE playlistName = ?;");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    query->addBindValue(QDateTime::currentMSecsSinceEpoch());
    query->addBindValue(pl->rating());
    query->addBindValue(pl->comment());
    bool result = query->exec();
    YLog::logQuery("updatePlaylistInfo", query);
    delete query;
    return result;
}


bool SLDbUpdater::updateCollectionInfo(CollectorItem *item, QSqlDatabase *database) {
    //I'm not allowing modification of trackPath and play count.
    QString statement("UPDATE Collections  trackName = ?, artistName = ?,"
                      "albumName = ?, trackDate = ?, genre = ?, comment = ?,"
                      "trackNum = ?, duration = ?, bitrate = ?, sampleRate = ?,"
                      "channels = ?, rating = ?, tags = ? "
                      "WHERE trackId = ?;");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    query->addBindValue(item->title());
    query->addBindValue(item->artist());
    query->addBindValue(item->album());
    query->addBindValue(item->date());
    query->addBindValue(item->genre());
    query->addBindValue(item->description());
    query->addBindValue(item->trackNumber());
    query->addBindValue(item->duration());
    query->addBindValue(item->bitrate());
    query->addBindValue(item->sampleRate());
    query->addBindValue(item->numChannels());
    query->addBindValue(Utils::getIntegerFromRating(item->rating()));
    query->addBindValue(item->playCount());
    query->addBindValue(item->tags());
    query->addBindValue(item->trackId());
    bool result = query->exec();
    YLog::logQuery("updateCollectionInfo", query);
    delete query;
    return result;
}


QSqlQuery *SLDbUpdater::getQuery(QSqlDatabase *database) {
    if(database != NULL) {
        return new QSqlQuery(*database);
    }
    return new QSqlQuery();
}


QSet<QString> *SLDbUpdater::getTagsFor(CollectorItem *item) {
    QSet<QString> *tags = new QSet<QString>();
    QStringList tagList = item->tags().split(",");
    for(int i = 0; i < tagList.size(); i++) {
        tags->insert(tagList.at(i));
    }
    return tags;
}


bool SLDbUpdater::updateTagFor(CollectorItem *item,
                               QSet<QString> *tags,
                               QSqlDatabase *database) {
    QSet<QString>::const_iterator it = tags->begin();
    QString tagBuilder;
    for(; it != tags->end(); ++it) {
        tagBuilder.append((*it));
    }
    QString statement("UPDATE Collection SET tags = ? WHERE trackId = ?;");
    QSqlQuery *query = getQuery(database);
    query->prepare(statement);
    query->addBindValue(tagBuilder);
    query->addBindValue(item->trackId());
    bool result = query->exec();
    YLog::logQuery("updateTagFor", query);
    delete query;
    return result;
}


bool SLDbUpdater::removeHistoryItems(QList<QString>& ids, QSqlDatabase *databse) {
    QString statement("DELETE FROM PlayHistory WHERE trackId = :trackId");
    QSqlQuery *query = getQuery(databse);
    query->prepare(statement);
    bool result = true;
    for(int i = 0; i < ids.size(); i++) {
        query->bindValue(":trackId", ids[i]);
        result &= query->exec();
        YLog::logQuery("removeHistoryItems", query);
    }
    delete query;
    return result;
}


bool SLDbUpdater::removeRecentAdditionItem(QList<QString>& ids, QSqlDatabase *databse) {
    QString statement("DELETE FROM RecentAdditions WHERE trackId = :trackId");
    QSqlQuery *query = getQuery(databse);
    query->prepare(statement);
    bool result = true;
    for(int i = 0; i < ids.size(); i++) {
        query->bindValue(":trackId", ids[i]);
        result &= query->exec();
        YLog::logQuery("removeRecentAdditionItem", query);
    }
    delete query;
    return result;
}
